CREATE FUNCTION [dbo].[LESS_Mojodi]
(
    @id NUMERIC(18, 0)
)
RETURNS @MOJODI TABLE
(
    A_Code VARCHAR(4),
    K_Code VARCHAR(15),
    Name1 NVARCHAR(100),
    req_pos REAL,
    Mojodi REAL,
    kasry REAL
)
AS
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.sysobjects
        WHERE name = 'TmpDrugh'
              AND xtype = 'U'
    )
        INSERT @MOJODI
        SELECT dbo.TmpHavaleh.Sender,
               dbo.TmpHavaleh.K_Code,
               dbo.KalaId.Name1,
               SUM(dbo.TmpHavaleh.K_Qty1) AS req_pos,
               dbo.Anbar.Mojodi,
               dbo.Anbar.Mojodi - SUM(dbo.TmpHavaleh.K_Qty1) AS [kasry]
        FROM dbo.Anbar WITH (NOLOCK)
            INNER JOIN dbo.TmpHavaleh WITH (NOLOCK)
                ON dbo.Anbar.A_Code = dbo.TmpHavaleh.Sender
                   AND dbo.Anbar.K_Code = dbo.TmpHavaleh.K_Code
            INNER JOIN dbo.KalaId WITH (NOLOCK)
                ON dbo.Anbar.K_Code = dbo.KalaId.K_Code
        GROUP BY dbo.TmpHavaleh.Sender,
                 dbo.TmpHavaleh.K_Code,
                 dbo.Anbar.Mojodi,
                 dbo.TmpHavaleh.CtrMojodi,
                 dbo.KalaId.Name1,
                 dbo.TmpHavaleh.Id_Havaleh
        HAVING (dbo.TmpHavaleh.CtrMojodi = '1')
               AND (dbo.TmpHavaleh.Id_Havaleh = @id)
               AND (dbo.Anbar.Mojodi - SUM(dbo.TmpHavaleh.K_Qty1) < 0)
               AND (LEN(dbo.TmpHavaleh.Sender) = 3);
    ELSE
        INSERT INTO @MOJODI
        SELECT PD.Sender,
               PD.K_Code,
               G.Name1,
               SUM(PD.K_Qty1) AS req_pos,
               W.Mojodi,
               W.Mojodi - (SUM(PD.K_Qty1) + SUM(ISNULL(drug.ReserveCount, 0))) AS [kasry]
        FROM dbo.Anbar W WITH (NOLOCK)
            INNER JOIN dbo.TmpHavaleh PD WITH (NOLOCK)
                ON W.A_Code = PD.Sender
                   AND W.K_Code = PD.K_Code
            INNER JOIN dbo.KalaId G WITH (NOLOCK)
                ON W.K_Code = G.K_Code
            OUTER APPLY
        (
            SELECT SUM(D.K_Qty1) ReserveCount
            FROM dbo.TmpDrugH H WITH (NOLOCK)
                JOIN dbo.TmpDrugHavaleh D
                    ON H.Id_Havaleh = D.Id_Havaleh
                       AND H.Kind_Flag = 1
                       AND H.kind_Save = 2
                       AND D.Sender = PD.Sender
                       AND D.K_Code = PD.K_Code
            GROUP BY D.K_Code
        ) AS drug
        GROUP BY PD.Sender,
                 PD.K_Code,
                 W.Mojodi,
                 PD.CtrMojodi,
                 G.Name1,
                 PD.Id_Havaleh
        HAVING (PD.CtrMojodi = '1')
               AND (PD.Id_Havaleh = @id)
               AND SUM(W.Mojodi) - (SUM(PD.K_Qty1) + SUM(ISNULL(drug.ReserveCount, 0))) < 0
               AND (LEN(PD.Sender) = 3);
    RETURN;
END
